﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HotelMatchUtility.Entity;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using Arch.Framework.Utility;

namespace HotelMatchUtility.Data
{
    public class CountryData
    {
        public static List<CountryEntity> GetCountryList()
        {
            List<CountryEntity> entityList = new List<CountryEntity>();
            try
            {
                Database db = null;
                db = DatabaseFactory.CreateDatabase("ProductDB_SELECT_pro");
                StringBuilder sql = new StringBuilder();

                sql.Append(@" Select Country,CountryName From ProductDB..Country(nolock) b where Country <> 1
                            order by country");
                CountryEntity countryEntity = null;
                DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());
                
                using (IDataReader idr = db.ExecuteReader(dbCommand))
                {
                    while (idr.Read())
                    {
                        countryEntity = new CountryEntity();
                        countryEntity.CountryId = ConvertHelper.ToInt32(idr["Country"]);
                        countryEntity.CountryName = ConvertHelper.ToString(idr["CountryName"]);
                        entityList.Add(countryEntity);
                    }
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                //Console.WriteLine(ex.)
            }

            return entityList;
        }

        public static List<CountryEntity> GetCountryListOrbitz()
        {
            List<CountryEntity> entityList = new List<CountryEntity>();
            try
            {
                Database db = null;
                db = DatabaseFactory.CreateDatabase("ProductDB_SELECT_pro");
                StringBuilder sql = new StringBuilder();

                sql.Append(@" select * from 
(select  distinct countryCode,b.ename ,a.CountryEnName,b.countryName,b.country from HotelInterfaceDB..HotelInterface_HotelGroupInfo(nolock) a
left join ProductDB..Country(nolock)  b on b.ename =a.CountryEnName
 where groupid = 32 ) a
 where ename is not null order by country
 ");
                CountryEntity countryEntity = null;
                DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());

                using (IDataReader idr = db.ExecuteReader(dbCommand))
                {
                    while (idr.Read())
                    {
                        countryEntity = new CountryEntity();
                        countryEntity.CountryId = ConvertHelper.ToInt32(idr["Country"]);
                        countryEntity.CountryName = ConvertHelper.ToString(idr["countryName"]);
                        countryEntity.CountryCode = Convert.ToString(idr["CountryCode"]);
                        countryEntity.CountryEnName = Convert.ToString(idr["CountryEnName"]);
                        entityList.Add(countryEntity);
                    }
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                //Console.WriteLine(ex.)
            }

            return entityList;
        }
    }
}
